/**
 * 文件名称:  BaseCarServiceImpl.java
 * 文件序号:  序号
 * 内容描述:  文件简单描述，多行换行书写
 * 版权声明:  山东鸿蝶信息技术有限公司
 * 创建用户:  zhangkai
 * 创建日期:  2017/2/27 14:17
 * 修改用户:  无
 * 修改日期:  无
 * 修改说明:  无
 * 文件版本:  V1.0
 * 文件路径:
 */

package com.hongdee.crm.base.service.impl;


import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.base.dao.ICarDao;
import com.hongdee.crm.base.service.IBaseCarService;
import com.hongdee.crm.carConfig.service.ITcrmCarConfService;
import com.hongdee.crm.task.service.ITCrmTimeTaskService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.xml.soap.SAAJMetaFactory;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.hongdee.common.util.DateUtils.addMonthLongTime;
import static com.hongdee.common.util.DateUtils.dayToLongTime;
import static com.hongdee.common.util.DateUtils.formatLongTime;

/**
 * 名  称:BaseCarSeriveImpl
 * 类  型: class
 * 描  述: 基盘查询服务
 * 路  径:com.hongdee.crm.base.service.impl
 * 作  者:zhangkai
 * 日  期:2017/2/27 14:17
 */
@Service
public class BaseCarServiceImpl implements IBaseCarService {
    private Logger logger = LoggerFactory.getLogger(BaseCarServiceImpl.class);

    @Autowired
    private ICarDao carDao;

    @Autowired
    private ITCrmTimeTaskService itCrmTaskService;
    @Autowired
    private ITcrmCarConfService confService;

    /**
     * 分页查询基盘车辆
     *
     * @param start
     * @param limit
     * @param queryParams
     * @param uid
     * @return
     */
    @Override
    public Map page(String start, String limit, Map queryParams, String uid) throws Exception {

        //获取公司ID
        /***
         * 如果指定了查询公司，没有指定 SA 则全询全部基盘
         * 如果指定了查询公司，为自己的公司，用户为管理员，查询全部基盘
         * 如果指定了查询公司，且为自己的公司，用户为普通用户，查询自己的基盘
         * */
        String companyId = StringUtil.formatEmpty(queryParams.get("companyId"));
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (queryParams.get("SA") != null && queryParams.get("SA").toString().length() > 0) {
            uid = queryParams.get("SA").toString();
        }

        //空的分页对象
        Map temp = new HashMap();
        temp.put("total", 0);
        temp.put("dataList", new ArrayList());
        System.out.println("基盘查询开始时间：" + DateUtils.getTimeStr());
        /***
         * 业务补充说明：
         * ***/
        //查询参数
        //时间参数
        System.out.println("基盘语句拼接开始时间：" + DateUtils.getTimeStr());

        String endDate = String.valueOf(queryParams.get("baseTime"));
        if (StringUtils.isEmpty(endDate) || "null".equals(endDate.toLowerCase())) {
            throw new Exception("请选择基盘结束时间");
        }

        //计算基盘日期2017-03-01
        long endTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long startTime = DateUtils.addMonthLongTime(endTime, -12);
        String startDate = DateUtils.formatLongTime(startTime, "yyyy-MM-dd");

        //计算基盘日期2017-03-01
        // long currentTime = DateUtils.parseTime(baseTs + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        System.out.println("基盘结束日期：" + startDate + "到" + endDate);


        StringBuffer sqlBuf = new StringBuffer();
        List<Object> params = new ArrayList();

        sqlBuf.append("select * from ( SELECT * FROM ( select vin, `plate_num`, `series`, `owner_name`, `from_company`,jccs,cz,car_age ,active,company_id" +
                " from " +
                "( SELECT c.vin,c.`plate_num`,c.`series`,c.`owner_name`,c.`from_company`,sum(o.`real_amount`) as cz, " +
                "COUNT(DISTINCT from_unixtime(o.`balance_date`/1000, '%Y-%m-%d')) as jccs");
        sqlBuf.append(",c.car_age,c.active,c.company_id ");
        sqlBuf.append(" from   t_crm_car c,");
//        sqlBuf.append(" (select * from t_bc_organ  where 1=1  ");
//        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlBuf, params, "");
//        sqlBuf.append(" ) bc ");
//        sqlBuf.append(" where car.company_id=bc.id )c ,");

        sqlBuf.append(" `t_crm_car_workorder` o where c.`vin`= o.`vin` and c.company_id=o.company_id and c.vin=o.vin and o.`real_amount`> '0.00' and c.series <> 'QT' ");

//        // 公司ID
        sqlBuf.append(" and c.company_id = ?");
        params.add(companyId);

        sqlBuf.append(" and from_unixtime(o.`balance_date`/1000, '%Y-%m-%d') BETWEEN ? and ? ");
        params.add(startDate);
        params.add(endDate);


        /* 新规则，按车驾号分配车辆 */

        //if (companyId.equals(TokenUtil.getCompanyId(uid))) {
        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);
        // }
//        Constant.VIN_TO_SQL(sqlBuf,uid,"c");

        System.out.println("分配车辆结束开始时间：" + DateUtils.getTimeStr());
        //车系
        //增加车型过滤
        Object seriesObj = queryParams.get("series");
        if (seriesObj != null) {
            List<String> series = (List<String>) seriesObj;
            if (series.size() > 0) {
                StringBuffer tempBuf = new StringBuffer();
                sqlBuf.append(" and ( ");
                for (Object str : series) {
                    tempBuf.append(" locate(? ,c.series) OR");
                    params.add((String) str);
                }
                sqlBuf.append(tempBuf.substring(0, tempBuf.length() - 2).toString());
                sqlBuf.append(" ) ");
            }
        }

        //增加车领计算
        Object ageObj = queryParams.get("age");
        if (null != ageObj) {
            List ageLs = (List) ageObj;
            if (ageLs.size() > 0) {
                sqlBuf.append(" AND(  ");
                StringBuffer tempAge = new StringBuffer(" c.car_age IN ( ");
                StringBuffer temAgeG = new StringBuffer(" c.car_age >= ? ");
                Object lsYear = null;
                for (Object obj : ageLs) {
                    if (Integer.valueOf(String.valueOf(obj)) > 8) {
                        lsYear = obj;
                    } else {
                        tempAge.append("?,");
                        params.add(Integer.valueOf((String) obj));
                    }
                }

                tempAge.append(" '')  ");

                if (null != lsYear) {
                    tempAge.append(" OR (").append(temAgeG).append(") ");
                    params.add(Integer.valueOf((String) lsYear));
                }
                sqlBuf.append(tempAge);
                sqlBuf.append(" )");
            }
        }

        //增加车里程计算
        Object runMileObj = queryParams.get("runMile");
        if (null != runMileObj) {
            List<String> runMileList = (List<String>) runMileObj;

            if (runMileList.size() > 0) {
                StringBuffer mileTempBuf = new StringBuffer();

                for (String mile : runMileList) {
                    String[] starts = mile.split("-");
                    if (starts.length == 2) {
                        mileTempBuf.append(" or c.run_mile between ? and ? ");
                        params.add(starts[0] + "0000");
                        params.add(starts[1] + "0000");
                    }
                }
                if (mileTempBuf.toString().length() > 0) {
                    sqlBuf.append("AND ( 1>2 ");
                    sqlBuf.append(mileTempBuf);
                    sqlBuf.append(" ) ");
                }


            }
        }

        //基盘类别
        //有效基盘 默认基盘
        String baseType = String.valueOf(queryParams.get("baseType"));

        switch (baseType) {
            case Constant.NEW_CAR:
                //新车增长
                sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        "dl.base_type = '" + Constant.NEW_CAR + "' AND  dl.tag_date BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;
            case Constant.SH_NEW:
                //社会招揽
                sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        "dl.base_type = '" + Constant.SH_NEW + "' AND dl.tag_date BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;
            case Constant.LOST_RETURN:
                //流失返还
                sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        "dl.base_type = '" + Constant.LOST_RETURN + "' AND dl.tag_date  BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;
            //查询有效基盘
            case "0":
                sqlBuf.append(" AND NOT EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        " dl.tag_date  BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;

            default:
                break;
        }


        //流失状态
        Object lostParamObj = queryParams.get("lost");
        if (null != lostParamObj) {
            Map<String, Object> lsMap = (Map<String, Object>) lostParamObj;
            if (lsMap.size() > 0) {
                //流失三个参数
                String lsType = String.valueOf(lsMap.get("type"));
                String lsStart = String.valueOf(lsMap.get("start"));
                String lsEnd = String.valueOf(lsMap.get("end"));
                switch (lsType) {
                    case "0":
                        if (!"null".equals(lsStart) && lsStart.length() > 0) {
                            //按天
                            sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND  ll.start_date >=  ? )");
                            params.add(lsStart);
                        }
                        if (!"null".equals(lsEnd) && lsEnd.length() > 0) {
                            //按天
                            // Long te = DateUtils.parseTime(lsEnd + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                            sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND  ll.start_date <=  ? )");
                            params.add(lsEnd);
                        }
                        break;
                    case "1":
                        //按月

                        if (!"null".equals(lsStart) && lsStart.length() > 0) {
                            //按天
                            //Long ts = DateUtils.parseTime(lsStart + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
                            sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND ll.start_date >=  ? and (( date_format(ll.start_date,'%y%m') != date_format(ll.end_date,'%y%m') or ll.end_date is null ) ))");
                            params.add(lsStart);
                        }
                        if (!"null".equals(lsEnd) && lsEnd.length() > 0) {
                            //按天
                            // Long te = DateUtils.parseTime(lsEnd + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                            sqlBuf.append(" AND EXISTS( SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND  ll.start_date <=?  and (( date_format(ll.start_date,'%y%m') != date_format(ll.end_date,'%y%m') or ll.end_date is null )  ))");
                            params.add(lsEnd);
                        }

                        break;
                    default:
                        break;
                }

            }
        }

        //车尾号
        Object whObj = queryParams.get("wh");
        if (null != whObj) {
            List<String> whList = (List<String>) whObj;
            if (whList.size() > 0) {
                String whTemp = "";
                for (String tm : whList) {
                    whTemp += tm;
                }
                sqlBuf.append(" AND  c.plate_num REGEXP ? ");
                params.add("[" + whTemp + "]$");
            }
        }
        //车属数
        Object wsObj = queryParams.get("ws");
        if (null != wsObj) {
            List<String> wsList = (List<String>) wsObj;
            if (wsList.size() > 0) {
                String whTemp = "";
                for (String tm : wsList) {
                    whTemp += tm;
                }
                sqlBuf.append(" AND c.plate_num REGEXP ? ");
                params.add(".*[" + whTemp + "][A-Za-z]*$");
            }
        }


        //行政区域

        Object areaIdObj = queryParams.get("xzarea");
        if (areaIdObj != null) {
            String areaStr = areaIdObj.toString().replace(",", "|").replace("[", "").replace("]", "").replace("\"", "").replaceAll(" ", "");
            System.out.println(areaStr);
            if (areaStr.length() > 0) {
                /**
                 * 区域查询由城市改为详细地址
                 carBuf.append(" AND c.owner_city regexp ?");
                 **/
                sqlBuf.append(" AND (c.owner_address regexp ? or  c.owner_city regexp ? )");

                params.add(areaStr);
                params.add(areaStr);
            }

        }

        //车架号
        String vin = String.valueOf(queryParams.get("vin"));
        if (!StringUtils.isEmpty(vin) && !"null".equals(vin.toLowerCase()))

        {
            sqlBuf.append("AND c.vin like ? ");
            params.add("%" + vin + "%");
        }

        //市场活动标签
        String tag = String.valueOf(queryParams.get("marketTag"));
        if (!StringUtils.isEmpty(tag) && !"null".equals(tag.toLowerCase()))

        {
            sqlBuf.append("AND c.market_tag = ? ");
            params.add(tag);
        }
        /**
         * 新增维系人、销售顾问过滤
         * Liu XiaoLong
         * 2017/9/18 10:58
         */
        // 销售顾问
        String saler = String.valueOf(queryParams.get("saler"));
        if (!StringUtils.isEmpty(saler))

        {
            sqlBuf.append("AND c.saler like ? ");
            params.add("%" + saler + "%");
        }
        // 维系人
        String wxId = String.valueOf(queryParams.get("wxId"));
        if (!StringUtils.isEmpty(wxId))

        {
            sqlBuf.append("AND c.wx_id like ? ");
            params.add("%" + wxId + "%");
        }


        sqlBuf.append(" group by c.id )temp order by car_age*1 asc ) aa GROUP BY aa.vin)temp ");

        System.out.println("基盘查询条年拼接完成时间：" + DateUtils.getTimeStr());
        System.out.println("基盘SQL开始时间：" + DateUtils.getTimeStr());
        System.out.println(sqlBuf.toString());
        Map page = this.carDao.page(sqlBuf.toString(), params.toArray(), start, limit);
        System.out.println("基盘SQL完成时间：" + DateUtils.getTimeStr());

        return page;
    }

    /**
     * 查询用户 分配的车辆信息
     *
     * @param uid
     * @return
     * @throws Exception
     */
    private Map<String, Object> refCar(String uid) throws Exception {
        String sql = "select  * from t_crm_car_ref where u_id = ? and status = '1' ";
        Map info = this.carDao.info(sql, uid);
        return info;
    }

    /**
     * 区间客户基盘数
     *
     * @param endDate
     * @return
     */
    @Override
    public Map baseCount(String endDate, String uid, String SA, String companyId) throws Exception {
        //获取公司ID
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (SA != null && SA.toString().length() > 0) {
            uid = SA;
        }
        //指定的时间转时间戳
        long endLongTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");

        //一年前
        long yinianqian = addMonthLongTime(endLongTime, -12);

        String preYear = DateUtils.formatLongTime(yinianqian, "yyyy-MM-dd");

        List params1 = new ArrayList();
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append("SELECT count(DISTINCT c.vin) baseCount FROM t_crm_car c WHERE c.series <> 'QT'  and  EXISTS " +
                "(SELECT 1 FROM t_crm_car_workorder wo WHERE wo.vin = c.vin and wo.company_id=c.company_id AND wo.real_amount > '0.00' " +
                " AND FROM_UNIXTIME(wo.BALANCE_DATE/1000,'%Y-%m-%d') between ? and ?) AND c.company_id = ?");
        /* 新规则，按车驾号分配车辆 */
//        Constant.VIN_TO_SQL(sqlBuf, uid, "c");
        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);
        params1.add(preYear);
        params1.add(endDate);
        params1.add(companyId);

        Map map1 = this.carDao.info(sqlBuf.toString(), params1.toArray());
        Map temp = new HashMap();
        temp.put("data", 0);
        temp.put("data", map1.get("baseCount"));
        return temp;
    }

    @Override
    public Map baseYearAgoCount(String endDate, String uid, String SA, String companyId) throws Exception {
        //获取公司ID
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (SA != null && SA.toString().length() > 0) {
            uid = SA;
        }
        //获得当前时间的时间戳
        String nowTimeStr = DateUtils.getTimeStr("yyyy-MM-dd");

        long currentTime = DateUtils.parseTime(nowTimeStr + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        //一年前
        long oneYearAgo = DateUtils.addMonthLongTime(currentTime, -12);
        long twoYearAgo = DateUtils.addMonthLongTime(currentTime, -24);

        String twoYearStr = DateUtils.formatLongTime(twoYearAgo);
        //转换为一年前的 yyyy
        long twoYear = DateUtils.dayToLongTime(twoYearStr);

        String oneYearStr = DateUtils.formatLongTime(oneYearAgo);
        long oneYear = DateUtils.dayToLongTime(oneYearStr);
        Map map1 = new HashMap();

        List params1 = new ArrayList();
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append("SELECT count(DISTINCT c.vin) baseCount FROM t_crm_car c WHERE  c.series <> 'QT' and EXISTS " +
                "(SELECT 1 FROM t_crm_car_workorder wo WHERE wo.car_id = c.id AND wo.real_amount > '0.00' AND wo.balance_date BETWEEN ? AND ?) AND c.company_id = ?");
        /* 新规则，按车驾号分配车辆 */
//        Constant.VIN_TO_SQL(sqlBuf, uid, "c");
        // if (companyId.equals(TokenUtil.getCompanyId(uid))) {
        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);
        //  }
        params1.add(twoYear);
        params1.add(oneYear);
        params1.add(companyId);

        map1 = this.carDao.info(sqlBuf.toString(), params1.toArray());
        Map temp = new HashMap();
        temp.put("data", 0);
        temp.put("data", map1.get("baseCount"));
        return temp;
    }

    /**
     * 区间基盘增长数量
     *
     * @param endDate
     * @return
     */

    @Override
    public Map baseGrowCount(String endDate, String uid, String SA, String companyId) throws Exception {
        //获取公司ID
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
            uid = SA;
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (SA != null && SA.toString().length() > 0) {
            uid = SA;
        }
        Map temp = new HashMap();
        temp.put("data", 0);

        //计算基盘日期2017-03-01
        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long preTime = DateUtils.addMonthLongTime(currentTime, -12);
        String startDate = DateUtils.formatLongTime(preTime, "yyyy-MM-dd");
        temp.put("start", startDate);
        temp.put("end", endDate);


        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        sqlBuf.append(" select d.`base_type`, ")
                .append("   COUNT(DISTINCT d.`car_id`) as count ")
                .append("  from t_crm_car c,`t_crm_car_workorder` o,`t_crm_car_base_detail` d ")
                .append("  where o.`car_id`= c.`id` and c.id= d.`car_id` and o.`real_amount`> '0.00'  and c.`series`<> 'QT' ")
                .append("   and from_unixtime(o.`balance_date`/1000, '%Y-%m-%d') BETWEEN ? and ? ")
                .append("   and d.`tag_date` BETWEEN ? and ? ")
                .append("  and c.`company_id`= ?  ");
        params.add(startDate);
        params.add(endDate);
        params.add(startDate);
        params.add(endDate);
        params.add(companyId);

        /* 新规则，按车驾号分配车辆 */
//        Constant.VIN_TO_SQL(sqlBuf, uid, "c");

        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);

//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//        switch (refType) {
//            case Constant.CAR_WH:
//                sqlBuf.append(" AND  c.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                sqlBuf.append(" AND c.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }
        sqlBuf.append("   GROUP BY d.base_type ");
        sqlBuf.append(" order by d.base_type asc  ");
        List rsList = this.carDao.list(sqlBuf.toString(), params.toArray());

        temp.put("dataList", rsList);
        return temp;
    }

    /**
     * 区间增长率
     *
     * @param endDate
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map baseGrowLv(String endDate, String uid, String SA, String companyId) throws Exception {

        Map temp = new HashMap();

        // 上一年基盘
        //计算基盘日期2017-03-01
        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long preTime = DateUtils.addMonthLongTime(currentTime, -12);
        String preTimeStr = DateUtils.formatLongTime(preTime, "yyyy-MM-dd");
        preTime = DateUtils.parseTime(preTimeStr + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Map baseCountMap = this.baseCount(preTimeStr, uid, SA, companyId);
        Map newMap = this.baseGrowCount(endDate, uid, SA, companyId);
        int baseCount = Integer.valueOf(String.valueOf(baseCountMap.get("data")));
        if (baseCount == 0) {
            temp.put("data", "0.00%");
            return temp;
        }

        Object growObj = newMap.get("dataList");
        if (null != growObj) {
            List<Map<String, Object>> growList = (List<Map<String, Object>>) growObj;
            int total = 0;
            for (Map<String, Object> ma : growList) {
                total += Integer.valueOf(String.valueOf(ma.get("count")));
            }

            BigDecimal resB = new BigDecimal(total).multiply(new BigDecimal(100)).divide(new BigDecimal(baseCount), 2, BigDecimal.ROUND_HALF_UP);
            temp.put("data", resB.toString() + "%");
            return temp;
        }
        return temp;
    }


    /**
     * 区间客户流失数量
     *
     * @param endDate
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map lostCount(String endDate, String uid, String SA, String companyId) throws Exception {
        //获取公司ID
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (SA != null && SA.toString().length() > 0) {
            uid = SA;
        }
        Map temp = new HashMap();
        temp.put("data", 0);


        //计算基盘日期2017-03-01
        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long preTime = DateUtils.addMonthLongTime(currentTime, -12);
        String startDate = DateUtils.formatLongTime(preTime, "yyyy-MM-dd");
        // preTime = DateUtils.parseTime(startDate + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        temp.put("start", startDate);
        temp.put("end", endDate);


        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        sqlBuf.append(" SELECT count(1) as count ")
                .append(" from t_crm_car c,")
                .append("  t_crm_car_lost_detail ld ")
                .append(" where ld.car_id= c.id ")
                .append(" and ld.start_date BETWEEN ? ")
                .append("  and ? ")
                .append(" and c.company_id= ?  and c.series <> 'QT' ");

        params.add(startDate);
        params.add(endDate);
        params.add(companyId);
        /* 新规则，按车驾号分配车辆 */
//        Constant.VIN_TO_SQL(sqlBuf, uid, "c");
        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//        switch (refType) {
//            case Constant.CAR_WH:
//                sqlBuf.append(" AND  c.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                sqlBuf.append(" AND c.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }


        Map result = this.carDao.info(sqlBuf.toString(), params.toArray());
        temp.put("data", result.get("count"));
        return temp;

    }

    /**
     * @Name: 区间车辆流失详情
     * @Author: Liu XiaoLong
     * @Date: 2017/9/18 15:00
     * @Remark:
     */
    @Override
    public Map lostCountDetail(String endDate, String uid, String SA, String companyId, String start, String limit) throws Exception {
        //获取公司ID
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (SA != null && SA.toString().length() > 0) {
            uid = SA;
        }
        Map temp = new HashMap();
        temp.put("data", 0);


        //计算基盘日期2017-03-01
        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long preTime = DateUtils.addMonthLongTime(currentTime, -12);
        String startDate = DateUtils.formatLongTime(preTime, "yyyy-MM-dd");
        // preTime = DateUtils.parseTime(startDate + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        temp.put("start", startDate);
        temp.put("end", endDate);


        StringBuffer sqlBuf = new StringBuffer();
        List params = new ArrayList();
        sqlBuf.append(" SELECT c.*,ld.start_date lostStartDate ")
                .append(" from t_crm_car c,")
                .append("  t_crm_car_lost_detail ld ")
                .append(" where ld.car_id= c.id ")
                .append(" and ld.start_date BETWEEN ? ")
                .append("  and ? ")
                .append(" and c.company_id= ?  and c.series <> 'QT' ");

        params.add(startDate);
        params.add(endDate);
        params.add(companyId);
        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);
        Map result = this.carDao.page(sqlBuf.toString(), params.toArray(), start, limit);
        //Map result = this.carDao.info(sqlBuf.toString(), params.toArray());
        //temp.put("data", result.get("count"));
        return result;
    }

    /**
     * 区间客户流失率
     *
     * @param endDate
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map lostLv(String endDate, String uid, String SA, String companyId) throws Exception {

        Map temp = new HashMap();
        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long preTime = DateUtils.addMonthLongTime(currentTime, -12);
        String preTimeStr = DateUtils.formatLongTime(preTime, "yyyy-MM-dd");
        preTime = DateUtils.parseTime(preTimeStr + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
        Map baseMap = this.baseYearAgoCount(preTimeStr, uid, SA, companyId);

        Map lostMap = this.lostCount(endDate, uid, SA, companyId);
        int baseCount = Integer.valueOf(String.valueOf(baseMap.get("data")));
        if (baseCount == 0) {
            temp.put("data", "0.00%");
            return temp;
        }
        int lsCount = Integer.valueOf(String.valueOf(lostMap.get("data")));
        String lv = new BigDecimal(lsCount).multiply(new BigDecimal(100)).divide(new BigDecimal(baseCount), 2, BigDecimal.ROUND_HALF_UP).toString() + "%";
        temp.put("data", lv);
        return temp;
    }

    /**
     * 年度活跃客度
     *
     * @param endDate
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map activeLv(String endDate, String uid, String SA, String companyId) throws Exception {

        Map temp = new HashMap();
        temp.put("data", "0.00%");
        //当前基盘
        Map baseMap = this.baseCount(endDate, uid, SA, companyId);
        int baseCount = Integer.valueOf(String.valueOf(baseMap.get("data")));
        if (baseCount == 0) {
            return temp;
        }
        //pre
        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        //一年前
        long preYearLong = DateUtils.addMonthLongTime(currentTime, -12);
        String preYearStr = DateUtils.formatLongTime(preYearLong, "yyyy-MM-dd");


        List params4 = new ArrayList();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT count(DISTINCT temp.vin) as activeCount FROM (SELECT vin,count(DISTINCT from_unixtime(o.balance_date/1000,'%Y-%m-%d')) AS jc FROM t_crm_car_workorder o WHERE o.company_id=? AND o.real_amount> '0.00' AND from_unixtime(o.balance_date/1000,'%Y-%m-%d') BETWEEN ? AND ? GROUP BY vin) temp WHERE temp.jc> 1");
//        Constant.VIN_TO_SQL(sqlStr,uid,"temp");
        //获取公司ID
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        /***如果选择了SA，则将账户切换为SA**/
        if (SA != null && SA.toString().length() > 0) {
            uid = SA;
        }
        confService.crmCarConfToSql(uid, sqlStr, "temp", companyId);
        params4.add(companyId);
        params4.add(preYearStr);
        params4.add(endDate);


//        //计算基盘日期2017-03-01
//        long currentTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
//        long preTime = DateUtils.addMonthLongTime(currentTime, -12);
//        String startDate = DateUtils.formatLongTime(preTime, "yyyy-MM-dd");
//        // preTime = DateUtils.parseTime(startDate + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
//        temp.put("start", startDate);
//        temp.put("end", endDate);
//
//
//        StringBuffer sqlBuf = new StringBuffer();
//        List params = new ArrayList();
//        sqlBuf.append("select count(1) as count ")
//                .append("  from(  ")
//                .append("         select count(DISTINCT FROM_UNIXTIME(o.`balance_date`/1000, '%Y-%m-%d')), c.id  ")
//                .append("  FROM t_crm_car c, `t_crm_car_workorder` o  ")
//                .append("   where o.`car_id`= c.id  ")
//                .append("  and FROM_UNIXTIME(o.`balance_date`/1000, '%Y-%m-%d') between ?  ")
//                .append("   and ? ")
//                .append("   and c.`company_id` = ? and c.`series` <> 'QT'  ");
//        params.add(startDate);
//        params.add(endDate);
//        params.add(TokenUtil.getCompanyId(uid));
//
//        Constant.VIN_TO_SQL(sqlBuf,uid,"c");
//        sqlBuf.append("  GROUP BY car_id  ")
//                .append(" HAVING count(DISTINCT FROM_UNIXTIME(o.`balance_date`/1000, '%Y-%m-%d'))> 1) temp   ");
//

        Map result = this.carDao.info(sqlStr.toString(), params4.toArray());
        int activeCount = Integer.valueOf(String.valueOf(result.get("activeCount")));
        String res = new BigDecimal(activeCount).multiply(new BigDecimal(100)).divide(new BigDecimal(baseCount), 2, BigDecimal.ROUND_HALF_UP).toString() + "%";
        temp.put("data", res);
        return temp;
    }


    public static void main(String[] args) {

        String nowTimeStr = DateUtils.getTimeStr("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
        try {
            Long nowLong = DateUtils.parseTimeZoneStr(nowTimeStr);
            long beforeDate = DateUtils.addMonthLongTime(nowLong, -12);
            System.out.println(beforeDate);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Map> areaCombo(String uid, String companyId) throws Exception {
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        List<Map> resList = new ArrayList<Map>();
        List params = new ArrayList();
        String sql = " select ar.area from t_bc_areas ar where ar.cityid = (SELECT o_city from t_bc_organ where id=?) ";
//        Map companyInfo = TokenUtil.getCompany(uid);
//        if (null == companyInfo) {
//            return resList;
//        }
        params.add(companyId);
        List<Map<String, Object>> mapList = this.carDao.list(sql, params.toArray());


        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("value", map.get("area"));
            temp.put("label", map.get("area"));
            resList.add(temp);
        }
        return resList;
    }

    /****
     * 查询全部要导出的基盘数据
     * @param queryParams
     * @param uid
     * @return
     */
    @Override
    public List<Map<String, Object>> listExport(Map queryParams, String uid) throws Exception {
        //获取公司ID
        String companyId = TokenUtil.getCompanyId(uid);
        /***如果选择了SA，则将账户切换为SA**/
        if (queryParams.get("SA") != null && queryParams.get("SA").toString().length() > 0) {
            uid = queryParams.get("SA").toString();
        }
        //空的分页对象
        Map temp = new HashMap();
        temp.put("total", 0);
        temp.put("dataList", new ArrayList());
        System.out.println("基盘查询开始时间：" + DateUtils.getTimeStr());
        /***
         * 业务补充说明：
         * ***/
        //查询参数
        //时间参数
        System.out.println("基盘语句拼接开始时间：" + DateUtils.getTimeStr());

        String endDate = String.valueOf(queryParams.get("baseTime"));
        if (StringUtils.isEmpty(endDate) || "null".equals(endDate.toLowerCase())) {
            throw new Exception("请选择基盘结束时间");
        }

        //计算基盘日期2017-03-01
        long endTime = DateUtils.parseTime(endDate + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        long startTime = DateUtils.addMonthLongTime(endTime, -12);
        String startDate = DateUtils.formatLongTime(startTime, "yyyy-MM-dd");

        //计算基盘日期2017-03-01
        // long currentTime = DateUtils.parseTime(baseTs + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
        System.out.println("基盘结束日期：" + startDate + "到" + endDate);


        StringBuffer sqlBuf = new StringBuffer();
        List<Object> params = new ArrayList();

        sqlBuf.append("select vin, `plate_num`, `series`, `owner_name`, `from_company`,jccs,cz from ( SELECT c.vin,c.`plate_num`,c.`series`,c.`owner_name`,c.`from_company`,sum(o.`real_amount`) as cz, COUNT(DISTINCT from_unixtime(o.`balance_date`/1000, '%Y-%m-%d')) as jccs")
                .append(" from t_crm_car c,`t_crm_car_workorder` o where c.`id`= o.`car_id`and o.`real_amount`> '0.00' and c.series <> 'QT' ");

        // 公司ID
        sqlBuf.append(" and c.company_id = ?");
        params.add(companyId);

        sqlBuf.append(" and from_unixtime(o.`balance_date`/1000, '%Y-%m-%d') BETWEEN ? and ? ");
        params.add(startDate);
        params.add(endDate);

//        sqlBuf.append(" SELECT temp.*  from ( SELECT c.vin ,c.plate_num ,c.series ,c.from_company, c.owner_name ,")
//                .append(" (select count(1) from t_crm_car_workorder  ol where ol.car_id  = c.id and ol.real_amount >0 and  FROM_UNIXTIME(ol.balance_date/1000,'%Y-%m-%d') BETWEEN ? and ?) as jccs ,")
//                .append(" (select sum(ol2.real_amount) from t_crm_car_workorder  ol2 where ol2.car_id  = c.id and ol2.real_amount >0 and FROM_UNIXTIME(ol2.balance_date/1000,'%Y-%m-%d') BETWEEN ? and ?) as cz ")
//                .append(" from  t_crm_car c   ");
//        params.add(startDate);
//        params.add(endDate);
//        params.add(startDate);
//        params.add(endDate);

        // 公司ID
//        sqlBuf.append("WHERE c.company_id = ?");
//        params.add(TokenUtil.getCompanyId(uid));
        //有效进厂工单
//        sqlBuf.append(" AND ")
//                .append(" EXISTS ( SELECT 1 from t_crm_car_workorder  o where c.id = o.car_id and o.real_amount > 0 and  FROM_UNIXTIME(o.balance_date/1000,'%Y-%m-%d') BETWEEN ? and ?  ) ");
//        params.add(startDate);
//        params.add(endDate);
//        System.out.println("分配车辆查询-拼接开始时间：" + DateUtils.getTimeStr());
        // 查询其分配的车辆
        //增加SA过滤
//        Object saObj = queryParams.get("SA");
//        Map info = new HashMap();
//        if (saObj != null) {
//            if (!String.valueOf(saObj).equals("")) {
//                info = refCar(String.valueOf(saObj));
//            } else {
//                info = refCar(uid);
//            }
//        } else {
//            info = refCar(uid);
//        }
        /* 新规则，按车驾号分配车辆 */
        confService.crmCarConfToSql(uid, sqlBuf, "c", companyId);
//        Constant.VIN_TO_SQL(sqlBuf, uid, "c");
        /* 旧规则，根本角色车辆分配规则暂时移除 */
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//
//        switch (refType) {
//            case Constant.CAR_WH:
//                sqlBuf.append(" AND  c.plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                sqlBuf.append(" AND c.plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }

        System.out.println("分配车辆结束开始时间：" + DateUtils.getTimeStr());
        //车系
        //增加车型过滤
        Object seriesObj = queryParams.get("series");
        if (seriesObj != null) {
            List<String> series = (List<String>) seriesObj;
            if (series.size() > 0) {
                sqlBuf.append(" AND c.series in ( ");
                for (String ser : series) {
                    sqlBuf.append("?,");
                    params.add(ser);
                }
                sqlBuf.append(" '')");
            }
        }

        //增加车领计算
        Object ageObj = queryParams.get("age");
        if (null != ageObj) {
            List<String> ages = (List<String>) ageObj;
            if (ages.size() > 0) {
                StringBuffer tempAgeBuf = new StringBuffer();
                for (String age : ages) {
                    //计算
                    int carMonth = Integer.valueOf(age) * 12;
                    Long ageStartTime = DateUtils.addMonthLongTime(startTime, -carMonth);
                    Long ageEndTime = DateUtils.addMonthLongTime(ageStartTime, 12);

                    switch (age) {
                        case "9":
                            tempAgeBuf.append(" or  c.guarantee_start_date <= ? ");
                            params.add(ageStartTime);
                            break;
                        default:
                            tempAgeBuf.append(" or (c.guarantee_start_date >=  ? and c.guarantee_start_date <= ? ) ");
                            params.add(ageStartTime);
                            params.add(ageEndTime);
                            break;
                    }

                }
                if (tempAgeBuf.toString().length() > 0) {
                    sqlBuf.append(" AND ( 1>2 ");
                    //去除第一个 or
                    sqlBuf.append(tempAgeBuf);
                    sqlBuf.append(" )");
                }
            }
        }

        //增加车里程计算
        Object runMileObj = queryParams.get("runMile");
        if (null != runMileObj) {
            List<String> runMileList = (List<String>) runMileObj;

            if (runMileList.size() > 0) {
                StringBuffer mileTempBuf = new StringBuffer();

                for (String mile : runMileList) {
                    String[] starts = mile.split("-");
                    if (starts.length == 2) {
                        mileTempBuf.append(" or c.run_mile between ? and ? ");
                        params.add(starts[0] + "0000");
                        params.add(starts[1] + "0000");
                    }
                }
                if (mileTempBuf.toString().length() > 0) {
                    sqlBuf.append("AND ( 1>2 ");
                    sqlBuf.append(mileTempBuf);
                    sqlBuf.append(" ) ");
                }


            }
        }

        //基盘类别
        //有效基盘 默认基盘
        String baseType = String.valueOf(queryParams.get("baseType"));

        switch (baseType) {
            case Constant.NEW_CAR:
                //新车增长
                sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        "dl.base_type = '" + Constant.NEW_CAR + "' AND  dl.tag_date BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;
            case Constant.SH_NEW:
                //社会招揽
                sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        "dl.base_type = '" + Constant.SH_NEW + "' AND dl.tag_date BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;
            case Constant.LOST_RETURN:
                //流失返还
                sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        "dl.base_type = '" + Constant.LOST_RETURN + "' AND dl.tag_date  BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;
            //查询有效基盘
            case "0":
                sqlBuf.append(" AND NOT EXISTS(SELECT 1 FROM t_crm_car_base_detail dl WHERE c.id = dl.car_id AND " +
                        " dl.tag_date  BETWEEN ? AND ? ) ");
                params.add(startDate);
                params.add(endDate);
                break;

            default:
                break;
        }


        //流失状态
        Object lostParamObj = queryParams.get("lost");
        if (null != lostParamObj) {
            Map<String, Object> lsMap = (Map<String, Object>) lostParamObj;
            if (lsMap.size() > 0) {
                //流失三个参数
                String lsType = String.valueOf(lsMap.get("type"));
                String lsStart = String.valueOf(lsMap.get("start"));
                String lsEnd = String.valueOf(lsMap.get("end"));
                switch (lsType) {
                    case "0":
                        if (!"null".equals(lsStart) && lsStart.length() > 0) {
                            //按天
                            sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND  ll.start_date >=  ? )");
                            params.add(lsStart);
                        }
                        if (!"null".equals(lsEnd) && lsEnd.length() > 0) {
                            //按天
                            // Long te = DateUtils.parseTime(lsEnd + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                            sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND  ll.start_date <=  ? )");
                            params.add(lsEnd);
                        }
                        break;
                    case "1":
                        //按月

                        if (!"null".equals(lsStart) && lsStart.length() > 0) {
                            //按天
                            //Long ts = DateUtils.parseTime(lsStart + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
                            sqlBuf.append(" AND EXISTS(SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND ll.start_date >=  ? and (( date_format(ll.start_date,'%y%m') != date_format(ll.end_date,'%y%m') or ll.end_date is null ) ))");
                            params.add(lsStart);
                        }
                        if (!"null".equals(lsEnd) && lsEnd.length() > 0) {
                            //按天
                            // Long te = DateUtils.parseTime(lsEnd + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                            sqlBuf.append(" AND EXISTS( SELECT 1 FROM t_crm_car_lost_detail ll WHERE c.id = ll.car_id AND  ll.start_date <=?  and (( date_format(ll.start_date,'%y%m') != date_format(ll.end_date,'%y%m') or ll.end_date is null )  ))");
                            params.add(lsEnd);
                        }

                        break;
                    default:
                        break;
                }

            }
        }

        //车尾号
        Object whObj = queryParams.get("wh");
        if (null != whObj) {
            List<String> whList = (List<String>) whObj;
            if (whList.size() > 0) {
                String whTemp = "";
                for (String tm : whList) {
                    whTemp += tm;
                }
                sqlBuf.append(" AND  c.plate_num REGEXP ? ");
                params.add("[" + whTemp + "]$");
            }
        }
        //车属数
        Object wsObj = queryParams.get("ws");
        if (null != wsObj) {
            List<String> wsList = (List<String>) wsObj;
            if (wsList.size() > 0) {
                String whTemp = "";
                for (String tm : wsList) {
                    whTemp += tm;
                }
                sqlBuf.append(" AND c.plate_num REGEXP ? ");
                params.add(".*[" + whTemp + "][A-Za-z]*$");
            }
        }


        //行政区域

        Object areaIdObj = queryParams.get("area");
        if (areaIdObj != null) {
            String areaStr = areaIdObj.toString().replace(",", "|").replace("[", "").replace("]", "").replace("\"", "").replaceAll(" ", "");
            System.out.println(areaStr);
            if (!StringUtils.isEmpty(areaStr)) {
                sqlBuf.append(" AND c.owner_city regexp ?");
                params.add(areaStr);
            }

        }

        //车架号
        String vin = String.valueOf(queryParams.get("vin"));
        if (!StringUtils.isEmpty(vin) && !"null".equals(vin.toLowerCase()))

        {
            sqlBuf.append("AND c.vin like ? ");
            params.add("%" + vin + "%");
        }
        sqlBuf.append(" group by c.id )temp ");
        System.out.println("基盘查询条年拼接完成时间：" + DateUtils.getTimeStr());
        System.out.println("基盘SQL开始时间：" + DateUtils.getTimeStr());
        System.out.println(sqlBuf.toString());
        return this.carDao.list(sqlBuf.toString(), params.toArray());
    }

    /**
     * 将基盘数据生成Execl
     *
     * @param lists
     * @param uid
     */
    @Override
    public XSSFWorkbook createExcel(List<Map<String, Object>> lists, String uid) {
        String[] excelHeaderRecordout = {"VIN", "车牌号", "车型", "车主", "本公司购车", "产值", "进场次数"};

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet = wb.createSheet("基盘车辆");


        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < lists.size(); i++) {
            /***vin, `plate_num`, `series`, `owner_name`, `from_company`,jccs,cz***/
            Map map = (Map) lists.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(String.valueOf(map.get("vin")));
            row.createCell(1).setCellValue(String.valueOf(map.get("plateNum")));
            row.createCell(2).setCellValue(String.valueOf(map.get("series")));
            row.createCell(3).setCellValue(String.valueOf(map.get("ownerName")));
            row.createCell(4).setCellValue(String.valueOf(map.get("fromCompany")).equals("是") ? "是" : "否");
            row.createCell(5).setCellValue(String.valueOf(map.get("cz")));
            row.createCell(6).setCellValue(String.valueOf(map.get("jccs")));
        }
        return wb;
    }

}

